/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import utils.DateUtil;
import utils.Utils;

/**
 *
 * @author daz
 */
public class TableDao {

    private DataSource dataSource;
    private String databaseName;
    JdbcTemplate jdbcTemplate;

    public TableDao(DataSource dataSource, String databaseName) {
        this.dataSource = dataSource;
        this.databaseName = databaseName;
        this.jdbcTemplate = new JdbcTemplate(dataSource, true);
    }

    public int testConnection() {
        String query = "select test from " + databaseName + ".Test";
        return jdbcTemplate.queryForInt(query);

    }

    public List<UserInfo> getUserInfo(String userName) {
        String query = "CALL " + databaseName + ".getUserInfo('" + userName + "')";
        return jdbcTemplate.query(query, new UserMapper());
    }

    public void marryTwoUser(String userName, String friend) {
        String query = "CALL " + databaseName + ".marryUser('" + userName + "','" + friend + "')";
        this.jdbcTemplate.execute(query);
    }

    public List<Test> testUnicode() {
        String query = "SELECT info FROM poker_online.test_unicode t";
        return this.jdbcTemplate.query(query, new TestUnicodeMapper());
    }

    public void deleteMessage(String userSend, String userReceive, Timestamp time) {
        String query = "CALL " + databaseName + ".deleteMessage('" + userSend + "','" + userReceive + "','" + time + "')";
        this.jdbcTemplate.execute(query);
    }

    void setReadMessage(String userSend, String userReceive, Timestamp timeSend) {
        String query = "CALL " + databaseName + ".setMessageRead"
                + "('" + userSend + "','" + userReceive + "','" + timeSend + "')";
        this.jdbcTemplate.execute(query);
    }

    void setAvatar(int val, String name) {
        String query = "CALL " + databaseName + ".setAvatar"
                + "(" + val + ",'" + name + "')";
        this.jdbcTemplate.execute(query);
    }

    List<SmsInfo> getSmsQueue() {
        String query = "CALL getSmsNap()";
        return jdbcTemplate.query(query, new SmsQueueMapper());
    }

    void logTransaction() {
        String query = "CALL logTransaction()";
        jdbcTemplate.execute(query);
    }

    List<UserInfo> getListUser(int number) {
        String query = "SELECT * FROM " + databaseName + ".user_manager u where id>=1207 and id <=2207 limit " + number;
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    List<UserInfo> getRobotUser(int number) {
        String query = "SELECT * FROM poker_online.user_manager "
                + " where is_online=0  "
                + " ORDER BY id  limit " + number;
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    int getNumberOnline() {
        return this.jdbcTemplate.queryForInt("select count(id) "
                + " from poker_online.user_manager "
                + " where is_online= 1;");
    }

    public void insertNewBuyItem(int isActivated, int kind, String userName, Timestamp time) {
        String query = "Call " + databaseName + ".insertNewBuyItem(" + isActivated + "," + kind + ",'" + userName + "','" + time + "')";
        this.jdbcTemplate.execute(query);
    }

    private static final class SmsQueueMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            SmsInfo smsInfo = new SmsInfo();
            smsInfo.setInfo(rs.getString("Info"));
            smsInfo.setServiceCode(rs.getString("Service_Number"));
            return smsInfo;
        }
    }

    private static final class TestUnicodeMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Test test = new Test();
            test.info = rs.getString("info");
            return test;
        }
    }

    private static final class UserMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserInfo userInfo = new UserInfo();
            userInfo.setUserName(rs.getString("user_name"));
            userInfo.setUserPassword(rs.getString("pass_word"));
            userInfo.setUserId(rs.getString("server_id"));
            userInfo.setIsplay(rs.getString("is_online"));
            userInfo.setMoney(rs.getInt("money"));
            userInfo.setGender(rs.getInt("gender"));
            userInfo.setLevel(Integer.parseInt(rs.getString("level")));
            userInfo.setNumberWin(Integer.parseInt(rs.getString("win")));
            userInfo.setNumbeLose(Integer.parseInt(rs.getString("lose")));
            userInfo.setStatus(rs.getString("status"));
            userInfo.setAvatarId(rs.getInt("has_avatar"));
            userInfo.setActivated(rs.getInt("is_activated"));
            return userInfo;
        }
    }

    public List<UserInfo> getUserRegisterInfo(String userName) {
        String query = "call " + databaseName + ".findUserName('" + userName + "')";
        return jdbcTemplate.query(query, new UserMapper());
    }

    public void setUserOnline(int state, String userName) {
        String query = "CALL " + databaseName + ".setUserOnline(" + state + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
        int avatarId = Utils.nextInt(6);
        query = "UPDATE " + databaseName + ".user_manager SET has_avatar=" + avatarId + " where user_name='" + userName + "'";
        this.jdbcTemplate.execute(query);
        insertNewBuyItem(1, avatarId + 4, userName, DateUtil.newTimestamp());
    }

    public void changeLoginId(long id, String userName) {
        String query = "Call " + databaseName + ".changeServerId(" + id + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void resetUserIsplay() {
        String query = "call " + databaseName + ".resetUserIsOnline()";
        this.jdbcTemplate.execute(query);
    }

    public void insertUserRegister(String userName, String password, String gender, String companyId) {
        String query = "CALL " + databaseName + ".insertUserRegister('" + userName + "','" + password + "'," + gender + "," + companyId + ")";
        // Debug.d("query:" + query);
        this.jdbcTemplate.execute(query);
    }

    public void updateMoney(int moneyChange, String userName) {
        String query = "CALL " + databaseName + ".updateMoney(" + moneyChange + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public List<MoneyInfo> getUserMoney(String userName) {
        String query = "CALL " + databaseName + ".getUserMoney('" + userName + "')";
        return this.jdbcTemplate.query(query, new MoneyMapper());
    }

    private static final class MoneyMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            MoneyInfo userInfo = new MoneyInfo();
            userInfo.setMoney(rs.getString("money"));
            return userInfo;
        }
    }

    public List<MateInfo> getMateInfo(String userName) {
        String query = "CALL " + databaseName + ".getMateInfo('" + userName + "')";
        return this.jdbcTemplate.query(query, new MateMapper());
    }

    private static final class MateMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            MateInfo mateInfo = new MateInfo();
            mateInfo.setFamilyId(rs.getInt("id"));
            mateInfo.setMateName1(rs.getString("user_name"));
            mateInfo.setMateName2(rs.getString("friend"));
            return mateInfo;
        }
    }

    public List<ChildInfo> getChildInfo(String name) {
        String query = "CALL " + databaseName + ".getFamilyInfo('" + name + "')";
        return this.jdbcTemplate.query(query, new ChildInfoMapper());
    }

    private static final class ChildInfoMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            ChildInfo childInfo = new ChildInfo();
            childInfo.setChildName(rs.getString("child_name"));
            childInfo.setGender(Integer.parseInt(rs.getString("child_gender")));
            return childInfo;
        }
    }

    public void updateLevel(String userName, int newLevel) {
        String query = "CALL " + databaseName + ".updateLevel('" + userName + "'," + newLevel + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void updateWin(String userName, int newWin) {
        String query = "CALL " + databaseName + ".updateWin('" + userName + "'," + newWin + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void updateLose(String userName, int newLose) {
        String query = "CALL " + databaseName + ".updateLose('" + userName + "'," + newLose + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void insertNewFriend(String userName1, String userName2) {
        String query = "CALL " + databaseName + ".insertNewFriend('" + userName1 + "','" + userName2 + "')";
        this.jdbcTemplate.execute(query);

    }

    public void marry(String userName1, String userName2) {
        String query = "CALL " + databaseName + ".changeMariedState('" + userName1 + "','" + userName2 + "')";
        this.jdbcTemplate.execute(query);
    }

    public List<FriendInfo> checkMaried(String userName1, String userName2) {
        String query = "CALL " + databaseName + ".checkFriend('" + userName1 + "','" + userName2 + "')";
        return this.jdbcTemplate.query(query, new CheckFriendMapper());
    }

    private static final class CheckFriendMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            FriendInfo friendInfo = new FriendInfo();
            friendInfo.setName(rs.getString("friend"));
            return friendInfo;
        }
    }

    public List<FriendInfo> getListFriend(String userName) {
        String query = "CALL " + databaseName + ".getListFriend('" + userName + "')";
        return this.jdbcTemplate.query(query, new ListFriendMapper());
    }

    private static final class ListFriendMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            FriendInfo friendInfo = new FriendInfo();
            friendInfo.setStatus(rs.getString("status"));
            friendInfo.setIsMaried(rs.getInt("is_maried"));
            friendInfo.setName(rs.getString("friend"));
            friendInfo.setOnline(rs.getInt("is_online"));
            friendInfo.setGedner(rs.getInt("gender"));
            friendInfo.setAvatarId(rs.getInt("has_avatar"));
            return friendInfo;
        }
    }

    public void insertNewMessenger(String userSend, String userReceive, String message, Timestamp timeSend, int kind) {
        String query = "CALL " + databaseName + ".insertNewMessage('" + userSend + "','" + userReceive + "','" + message + "','" + timeSend + "'," + kind + ")";
        this.jdbcTemplate.execute(query);
    }

    public List<UserMessage> getMessage(String userReceive) {
        String query = "CALL " + databaseName + ".getMessage('" + userReceive + "')";
        return this.jdbcTemplate.query(query, new MessageMapper());
    }

    private static final class MessageMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserMessage message = new UserMessage();
            message.setUserSend(rs.getString("user_send"));
            message.setMessage(rs.getString("messenger"));
            message.setKind(rs.getInt("kind"));
            message.setIsRead(rs.getInt("is_read"));
            message.setTime(rs.getTimestamp("time_send"));
            return message;
        }
    }

    public List<UserInfo> getTopLevel() {
        String query = "CALL " + databaseName + ".getTopLevel()";
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    public List<UserInfo> getTopRich() {
        String query = "CALL " + databaseName + ".getTopRich()";
        return this.jdbcTemplate.query(query, new UserMapper());
    }
//
//    private static final class UserInfoMapper implements RowMapper {
//
//        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
//            UserInfo userInfo = new UserInfo();
//            userInfo.setUserName(rs.getString("user_name"));
//            userInfo.setGender(rs.getInt("gender"));
//            userInfo.setMoney(rs.getInt("money"));
//            userInfo.setLevel(rs.getInt("level"));
//            userInfo.setAvatarId(rs.getInt("has_avatar"));
//            return userInfo;
//        }
//    }

    public void insertUserStatus(String userName, String status) {
        String query = "CALL " + databaseName + ".insertUserStatus('" + userName + "','" + status + "')";
        this.jdbcTemplate.execute(query);


    }

    public List<ListItem> getItemInfo() {
        String query = "select * from " + databaseName + ".item";
        return this.jdbcTemplate.query(query, new ItemInfoMapper());
    }

    private static final class ItemInfoMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            ListItem itemInfo = new ListItem();
            itemInfo.setItemname(rs.getString("name"));
            itemInfo.setItemId(rs.getInt("kind"));
            itemInfo.setLuckyPercent(rs.getInt("lucky_percent"));
            itemInfo.setPrice(rs.getInt("price"));
            itemInfo.setTimeLimit(rs.getInt("time_limit"));
            return itemInfo;
        }
    }

    public void insertNewBuyItem(int isActivated, int kind, String userName) {
        String query = "Call " + databaseName + ".insertNewBuyItem(" + isActivated + "," + kind + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void insertNewItemHistory(int kindItem, int kindAct, String userName) {
        String query = "Call " + databaseName + ".insertNewItemHistory(" + kindItem + "," + kindAct + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public List<Item> getUserListItem(String userName) {
        String query = "CALL " + databaseName + ".getUserItem('" + userName + "')";
        return this.jdbcTemplate.query(query, new ItemListMapper());
    }

    private static final class ItemListMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Item item = new Item();
            item.setActivated(rs.getInt("is_actived"));
            item.setId(rs.getInt("kind"));
            item.setLuckyPercent(rs.getInt("lucky_percent"));
            item.setPrice(rs.getInt("price"));
            item.setStartTime(rs.getTimestamp("time_start"));
            return item;
        }
    }

    public List<ShopHistoryItem> getUserShopHistory(String userName) {
        String query = "CALL " + databaseName + ".getUserShopHistory('" + userName + "')";
        return this.jdbcTemplate.query(query, new ShopHistoryListMapper());
    }

    private static final class ShopHistoryListMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int i) throws SQLException {
            int kindItem = rs.getInt("kind_item");
            int kindAct = rs.getInt("kind_act");
            Timestamp time = rs.getTimestamp("time_act");
            ShopHistoryItem item = new ShopHistoryItem(kindItem, kindAct, time.toString());
            return item;
        }
    }

    public void activateItem(Timestamp newTime, Timestamp oldTime, String userName, int kind) {
        String query = "CALL " + databaseName + ".activateItem('" + newTime + "','" + oldTime + "','" + userName + "'," + kind + ")";
        this.jdbcTemplate.execute(query);
    }

    public void deleteItemUsed(String name, Timestamp timeUse) {
        String query = "CALL " + databaseName + ".deleteUserItem('" + name + "','" + timeUse + "')";
        this.jdbcTemplate.execute(query);
    }

    public void inserChild(int familyId, String childName, int childGender) {
        String query = "CALL " + databaseName + ".insertFamilyInfo(" + familyId + ",'" + childName + "'," + childGender + ");";
        this.jdbcTemplate.execute(query);
    }

    void changePass(String userName, String newPass) {
        String query = "CALL changePass('" + userName + "','" + newPass + "')";
        jdbcTemplate.execute(query);
    }

    void activateAcc(String name) {
        String query = "CALL " + databaseName + ".activateAcc('" + name + "')";
        jdbcTemplate.execute(query);
    }
}
